const express = require('express');
const router = express.Router();

// 第三方mysql数据库并创建链接池
const mysql = require('mysql');
const db = mysql.createPool({
    host: '127.0.0.1',
    user: 'root',
    password: 'root',
    database: 'qianduan',
    port: '3306'
});

// get 请求列表
router.get('/user', (req, res) => {
    // 搜索查询
    let search = JSON.parse(req.query.search);
    let where = [];
    for (let k in search) {
        if (search[k].trim()) {
            if (k == 'start') {
                where.push(` and stu_age > ${parseInt(search[k])}`);
            } else if (k == 'end') {
                where.push(` and stu_age < ${parseInt(search[k])}`);
            } else if (k == 'stu_sex') {
                where.push(` and stu_sex = '${search[k]}'`);
            } else {
                where.push(` and ${k} like '%${search[k].trim()}%'`);
            }
        }
    }
    // 分页
    let page = req.query.page ? parseInt(req.query.page) : 1;
    let per_page = req.query.per_page ? parseInt(req.query.per_page) : 3;
    let count = 0;
    db.query(`select count(*) as count from student where status in (?, ?) ${where.join('')} order by stu_id desc`, [0, 1], (err, results) => {
        if (err) return res.send({
            status: 1,
            msg: `数据查询错误：${err.message}`
        });
        count = results[0].count;
    })
    db.query(`select * from student where status in (?, ?) ${where.join('')} order by stu_id desc limit ?, ?`, [0, 1, (page - 1) * per_page, per_page], (err, results) => {
        if (err) return res.send({
            status: 1,
            msg: `数据查询错误：${err.message}`
        });
        res.send({
            status: 0,
            msg: '数据库查询成功',
            count: count,
            data: results
        });
    });
});

// post 创建用户
router.post('/user/create', (req, res) => {
    db.query(`insert student set ?`, req.body, (err, results) => {
        if (err) return res.send({
            status: 1,
            msg: `添加失败：${err.message}`
        });
        res.send({
            status: 0,
            msg: '添加成功'
        });
    });
});

// get 请求编辑
router.get('/user/edit/:id', (req, res) => {
    let stu_id = req.params.id;
    db.query(`select * from student where stu_id = ?`, stu_id, (err, results) => {
        if (err) return res.send({
            status: 1,
            msg: `请求失败：${err.message}`
        });
        res.send({
            status: 0,
            msg: `请求成功`,
            data: results
        });
    });
})

// put 保存编辑
router.put('/user/edit/:id/update', (req, res) => {
    db.query(`update student set ? where stu_id = ?`, [req.body, req.body.stu_id], (err, results) => {
        if (err) return res.send({
            status: 1,
            msg: `修改失败：${err.message}`
        });
        res.send({
            status: 0,
            msg: `修改成功`
        });
    });
})

// delete 请求删除
router.delete('/user/delete/:id', (req, res) => {
    let stu_id = req.params.id;
    db.query(`update student set ? where stu_id = ?`, [{status: 1}, stu_id], (err, results) => {
        if (err) return res.send({
            status: 1,
            msg: `软删除失败：${err.message}`
        });
        res.send({
            status: 0,
            msg: `软删除成功`
        });
    });
})

// delete 请求批量软删
router.delete('/user/delete', (req, res) => {
    db.query(`update student set ? where stu_id in (${req.body.join(',')})`, {status: 1}, (err, results) => {
        if (err) return res.send({
            status: 1,
            msg: `批量软删除失败：${err.message}`
        });
        res.send({
            status: 0,
            msg: `批量软删除成功`
        });
    });
})

// patch 请求还原
router.patch('/user/patch/:id', (req, res) => {
    let stu_id = req.params.id;
    db.query(`update student set ? where stu_id = ?`, [{status: 0}, stu_id], (err, results) => {
        if (err) return res.send({
            status: 1,
            msg: `还原失败：${err.message}`
        });
        res.send({
            status: 0,
            msg: `还原成功`
        });
    });
})

module.exports = router;